package com.homestay.dao.impl;

import com.homestay.beans.BookInfo;
import com.homestay.beans.Books;
import com.homestay.dao.BooksDao;
import com.homestay.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

public class BooksDaoImpl implements BooksDao {
    QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());

    @Override
    public int addBooks(Books book) {
        try {
            return queryRunner.update("INSERT INTO tb_books (vip_id, enterTime, exitTime, home_id, createTime) VALUES (?, ?, ?, ?, now())",
                    book.getVip_id(), book.getEnterTime(), book.getExitTime(), book.getHome_id());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int deleteBooks(int id) {
        try {
            return queryRunner.update("DELETE FROM tb_books WHERE id = ?", id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int updateBooks(Books book) {
        try {
            return queryRunner.update("UPDATE tb_books SET vip_id = ?, enterTime = ?, exitTime = ?, home_id = ? WHERE id = ?",
                    book.getVip_id(), book.getEnterTime(), book.getExitTime(), book.getHome_id(), book.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Books> getBooks(String phone) {
        try {
            if (phone == null || phone.isEmpty()) {
                return queryRunner.query("select * from tb_books", new BeanListHandler<Books>(Books.class));
            } else {
                return queryRunner.query("SELECT b.* FROM tb_books b JOIN tb_vip v ON b.vip_id = v.id WHERE v.phone like ?",
                        new BeanListHandler<>(Books.class), "%" + phone + "%");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Books getBooksById(int id) {
        try {
            return queryRunner.query("SELECT * FROM tb_books WHERE id = ?",
                    new BeanHandler<>(Books.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<BookInfo> getBookInfo(String phone) {
        try {
            if (phone == null || phone.isEmpty()) {
                return queryRunner.query("SELECT b.id,b.vip_id,v.name,v.sex,v.card,v.phone,b.enterTime,b.exitTime,b.home_id,h.h_type,h.num FROM tb_books b JOIN tb_vip v on b.vip_id=v.id JOIN tb_home h on b.home_id=h.id",
                        new BeanListHandler<>(BookInfo.class));
            } else {
                return queryRunner.query("SELECT b.id,b.vip_id,v.name,v.sex,v.card,v.phone,b.enterTime,b.exitTime,b.home_id,h.h_type,h.num FROM tb_books b JOIN tb_vip v on b.vip_id=v.id JOIN tb_home h on b.home_id=h.id WHERE v.phone like ?",
                        new BeanListHandler<>(BookInfo.class), "%" + phone + "%");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public BookInfo getBookInfoById(String id) {
        try {
            if(id==null||id.isEmpty()){
                return null;
            }else {
                return queryRunner.query("SELECT b.id,b.vip_id,v.name,v.sex,v.card,v.phone,b.enterTime,b.exitTime,b.home_id,h.h_type,h.num FROM tb_books b JOIN tb_vip v on b.vip_id=v.id JOIN tb_home h on b.home_id=h.id WHERE b.id=?",
                        new BeanHandler<>(BookInfo.class), id  );
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Books getBooksByVipId(int vip_id) {
        try {
            return queryRunner.query("SELECT * FROM tb_books WHERE vip_id = ?",
                    new BeanHandler<>(Books.class), vip_id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
